/*
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 * 
 * The Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
 *
 * The Initial Developer of the Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
 * Portions created by Mark A. Kobold are Copyright (C) 2000-2007. All Rights Reserved.
 *
 * Contributor(s): 
 *  Mark A. Kobold [mkobold <at> isqlviewer <dot> com].
 *  
 * If you didn't download this code from the following link, you should check
 * if you aren't using an obsolete version: http://www.isqlviewer.com
 */
package org.isqlviewer.model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.TreeSet;

import javax.swing.tree.DefaultTreeModel;

import org.isqlviewer.util.LocalMessages;

/**
 * JDBC Schema Model that represents a Swing JTree compatible representation of JDBC structures.
 * <p>
 * 
 * @author Mark A. Kobold &lt;mkobold at isqlviewer dot com&gt;
 * @version 1.0
 */
public class JdbcSchemaTreeModel extends DefaultTreeModel {

    private static final long serialVersionUID = -3951771268602130376L;
    private static final String RESOURCE_BUNDLE = "org.isqlviewer.model.ResourceBundle";
    private static final LocalMessages messages = new LocalMessages(RESOURCE_BUNDLE);

    private DatabaseMetaData jdbcMetadata = null;
    private String catalog = null;
    private String schema = null;

    private SchemaNode rootNode = null;
    private boolean showProcedures = true;
    private boolean metaEnabled = true;
    private boolean showTables = true;

    public String getCurrentCatlog() {

        return catalog;
    }

    public String getCurrentSchema() {

        return schema;
    }

    public JdbcSchemaTreeModel() {

        super(new SchemaNode(null, true, SchemaNodeType.ROOT));
        rootNode = (SchemaNode) getRoot();
    }

    public void updateConnection(Connection cnx, boolean setContext) throws SQLException {

        if (cnx == null && jdbcMetadata == null) {
            rootNode.removeAllChildren();
            return;
        }

        if (cnx != null) {
            jdbcMetadata = cnx.getMetaData();
            rootNode.setUserObject("Connection");
            rootNode.setAllowsChildren(true);
            if (setContext) {
                try {
                    setSchema(jdbcMetadata.getUserName(), false);
                } catch (Throwable t) {
                    setSchema(null, false);
                }

                try {
                    setCatalog(cnx.getCatalog(), false);
                } catch (Throwable t) {
                    setCatalog(null, false);
                }
            }
        } else {
            clear();
        }
    }

    public synchronized void setCatalog(String pCatalog) {

        setCatalog(pCatalog, true);
    }

    public synchronized void setSchema(String pSchema) {

        setSchema(pSchema, true);
    }

    public synchronized void setCatalog(String pCatalog, boolean reload) {

        if (pCatalog != null) {
            catalog = pCatalog;
        } else {
            catalog = null;
        }

        if (reload) {
            reload();
        }
    }

    public synchronized void setSchema(String pSchema, boolean reload) {

        if (pSchema != null)
            schema = pSchema;
        else
            schema = null;
        if (reload)
            reload();
    }

    @Override
    public synchronized void reload() {

        rootNode.removeAllChildren();
        if (jdbcMetadata != null) {
            initializeDefaultNodes(metaEnabled, showTables, showProcedures);
        }
        reload(rootNode);
    }

    public synchronized void loadChildNodes(SchemaNode parentNode) {

        if (parentNode == null) {
            throw new NullPointerException();
        }

        SchemaNodeType nodeType = parentNode.getNodeType();
        switch (nodeType) {
            case TABLE_TYPE :
                loadTableTypeChildren(parentNode);
                break;
            case PROCEDURE_SET :
                loadProcedureChildren(parentNode);
                break;
            case TABLE :
                loadTableChildren(parentNode);
                break;
            case COLUMN_SET :
                loadColumnChildren(parentNode);
                break;
            case DATA_TYPE_SET :
                loadDataTypeChildren(parentNode);
                break;
            case UDT_SET_DISTINCT :
                loadUserDataTypeChildren(parentNode, Types.DISTINCT);
                break;
            case UDT_SET_JAVA_OBJECT :
                loadUserDataTypeChildren(parentNode, Types.JAVA_OBJECT);
                break;
            case UDT_SET_STRUCT :
                loadUserDataTypeChildren(parentNode, Types.STRUCT);
                break;
            default :
                break;
        }
    }

    public boolean isMetadataEnabled() {

        return metaEnabled;
    }

    public boolean isProceduresEnabled() {

        return showProcedures;
    }

    public boolean isTablesEnabled() {

        return showTables;
    }

    public void setMetadataEnabled(boolean b) {

        metaEnabled = b;
    }

    public void setProceduresEnabled(boolean b) {

        showProcedures = b;
    }

    public void setTablesEnabled(boolean b) {

        showTables = b;
    }

    /**
     * @return
     */
    public DatabaseMetaData getMetaData() {

        return jdbcMetadata;
    }

    /**
     * Clears all items from the tree.
     * <p>
     */
    public void clear() {

        rootNode.setUserObject("");
        rootNode.removeAllChildren();
        jdbcMetadata = null;
        catalog = null;
        schema = null;
        reload();
    }

    protected void initializeDefaultNodes(boolean showDataTypes, boolean showTableTypes, boolean showStoredProcedures) {

        String nodeText = null;
        if (showDataTypes) {
            SchemaNode schemaNode = null;

            schemaNode = new SchemaNode(messages.format("schema.userdatatypes.node"), true, SchemaNodeType.UDT_SET);
            insertNodeInto(schemaNode, rootNode, 0);

            nodeText = messages.format("schema.userdatatypes_distinct.node");
            SchemaNode udt = new SchemaNode(nodeText, true, SchemaNodeType.UDT_SET_DISTINCT);
            udt.add(createNoncachedNode());
            insertNodeInto(udt, schemaNode, 0);

            nodeText = messages.format("schema.userdatatypes_java_object.node");
            udt = new SchemaNode(nodeText, true, SchemaNodeType.UDT_SET_JAVA_OBJECT);
            udt.add(createNoncachedNode());
            insertNodeInto(udt, schemaNode, 0);

            nodeText = messages.format("schema.userdatatypes_struct.node");
            udt = new SchemaNode(nodeText, true, SchemaNodeType.UDT_SET_STRUCT);
            udt.add(createNoncachedNode());
            insertNodeInto(udt, schemaNode, 0);

            schemaNode = new SchemaNode(messages.format("schema.datatypes.node"), true, SchemaNodeType.DATA_TYPE_SET);
            schemaNode.add(createNoncachedNode());
            insertNodeInto(schemaNode, rootNode, 0);
        }

        if (showTableTypes) {
            ResultSet resultSet = null;
            Collection<String> tableTypes = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER);
            try {
                resultSet = jdbcMetadata.getTableTypes();
                while (resultSet.next()) {
                    tableTypes.add(resultSet.getString("TABLE_TYPE"));
                }
            } catch (SQLException sqle) {
                // TODO Do Something here...
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException ignored) {
                    }
                }
            }

            for (String type : tableTypes) {
                String[] typeSet = new String[]{type};
                try {
                    resultSet = jdbcMetadata.getTables(catalog, schema, null, typeSet);
                    if (resultSet.next()) {
                        // Threre are tables that exist for this type in the current catalog/schema //
                        SchemaNode tabletypeNode = new SchemaNode(type.trim(), true, SchemaNodeType.TABLE_TYPE);
                        tabletypeNode.add(createNoncachedNode());
                        insertNodeInto(tabletypeNode, rootNode, 0);
                    }
                } catch (SQLException sqle) {
                } finally {
                    if (resultSet != null) {
                        try {
                            resultSet.close();
                        } catch (SQLException ignored) {
                        }
                    }
                }
            }
        }

        if (showStoredProcedures) {
            ResultSet resultSet = null;
            try {
                resultSet = jdbcMetadata.getProcedures(catalog, schema, null);
                if (resultSet.next()) {
                    // Threre are stored procedures that exist for this type in the current catalog/schema //
                    nodeText = messages.format("schema.storedprocedures.node");
                    SchemaNode procedureNode = new SchemaNode(nodeText, true, SchemaNodeType.PROCEDURE_SET);
                    procedureNode.add(new SchemaNode(SchemaNodeType.NON_CACHED));
                    insertNodeInto(procedureNode, rootNode, 0);
                }
            } catch (SQLException sqle) {
                nodeText = messages.format("schema.storedprocedures.node");
                SchemaNode procedureNode = new SchemaNode(nodeText, false, SchemaNodeType.PROCEDURE_SET);
                procedureNode.setHasError(true);
                insertNodeInto(procedureNode, rootNode, 0);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException ignored) {
                    }
                }
            }
        }
    }

    private SchemaNode createNoncachedNode() {

        return new SchemaNode(messages.getMessage("schema.noncached.node"), false, SchemaNodeType.NON_CACHED);
    }

    private void loadTableTypeChildren(SchemaNode tabletypeNode) {

        tabletypeNode.removeAllChildren();
        ResultSet resultSet = null;
        if (showTables) {
            String tableType = tabletypeNode.getUserObject();
            try {
                resultSet = jdbcMetadata.getTables(catalog, schema, null, new String[]{tableType});
                while (resultSet.next()) {
                    String tableName = resultSet.getString("TABLE_NAME");
                    SchemaNode tableNode = new SchemaNode(tableName.trim(), true, SchemaNodeType.TABLE);
                    insertNodeInto(createNoncachedNode(), tableNode, 0);
                    insertNodeInto(tableNode, tabletypeNode, 0);
                }
            } catch (SQLException sqle) {
                tabletypeNode.setHasError(true);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException ignored) {
                    }
                }
                reload(tabletypeNode);
            }
        }
    }

    private void loadDataTypeChildren(SchemaNode typesetNode) {

        typesetNode.removeAllChildren();
        ResultSet resultSet = null;
        try {
            resultSet = jdbcMetadata.getTypeInfo();
            while (resultSet.next()) {
                String tableName = resultSet.getString("TYPE_NAME");
                SchemaNode tableNode = new SchemaNode(tableName.trim(), true, SchemaNodeType.DATA_TYPE);
                insertNodeInto(tableNode, typesetNode, 0);
            }
        } catch (SQLException sqle) {
            typesetNode.setHasError(true);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
            reload(typesetNode);
        }
    }

    private void loadUserDataTypeChildren(SchemaNode usertypesetNode, int dataType) {

        usertypesetNode.removeAllChildren();
        ResultSet resultSet = null;
        int[] type = new int[]{dataType};
        try {
            resultSet = jdbcMetadata.getUDTs(catalog, schema, null, type);
            while (resultSet.next()) {
                String tableName = resultSet.getString("TYPE_NAME");
                SchemaNode tableNode = new SchemaNode(tableName.trim(), true, SchemaNodeType.DATA_TYPE);
                insertNodeInto(tableNode, usertypesetNode, 0);
            }
        } catch (SQLException sqle) {
            usertypesetNode.setHasError(true);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
            reload(usertypesetNode);
        }
    }

    private void loadProcedureChildren(SchemaNode procedureNode) {

        procedureNode.removeAllChildren();
        ResultSet resultSet = null;
        if (showProcedures) {
            try {
                resultSet = jdbcMetadata.getProcedures(catalog, schema, null);
                while (resultSet.next()) {
                    String tableName = resultSet.getString("PROCEDURE_NAME");
                    SchemaNode tableNode = new SchemaNode(tableName.trim(), false, SchemaNodeType.PROCEDURE);
                    insertNodeInto(tableNode, procedureNode, 0);
                }
            } catch (SQLException sqle) {
                procedureNode.setHasError(true);
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException ignored) {
                    }
                }
                reload(procedureNode);
            }
        }
    }

    private void loadTableChildren(SchemaNode tableNode) {

        tableNode.removeAllChildren();
        String nodeText = null;

        nodeText = messages.format("schema.columns.node");
        SchemaNode columnsetNode = new SchemaNode(nodeText, true, SchemaNodeType.COLUMN_SET);
        insertNodeInto(columnsetNode, tableNode, 0);
        insertNodeInto(createNoncachedNode(), columnsetNode, 0);

        // TODO Add a profile preference for service API level so that if a service is set to JDBC 2.x or 1.0 :-\ we
        // won't call JDBC 3.0 functions like getSuperTables this would reduce false exceptions being logged.
        loadJdbc10TableChildren(tableNode, tableNode.getUserObject());
        if (Boolean.getBoolean("isql.jdbc3.enabled")) {
            loadJdbc30TableChildren(tableNode, tableNode.getUserObject());
        }
        reload(tableNode);
    }

    private void loadJdbc10TableChildren(SchemaNode tableNode, String table) {

        String nodeText = null;
        SchemaNode schemaNode = null;
        ResultSet resultSet = null;

        try {
            resultSet = jdbcMetadata.getIndexInfo(catalog, schema, table, false, false);
            if (resultSet.next()) {
                nodeText = messages.format("schema.indicies.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.INDEX);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.indicies.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.INDEX);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

        try {
            resultSet = jdbcMetadata.getPrimaryKeys(catalog, schema, table);
            if (resultSet.next()) {
                nodeText = messages.format("schema.primarykeys.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.PRIMARY_KEYS);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.primarykeys.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.PRIMARY_KEYS);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

        try {
            resultSet = jdbcMetadata.getExportedKeys(catalog, schema, table);
            if (resultSet.next()) {
                nodeText = messages.format("schema.foreignkeys.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.EXPORTED_KEYS);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.foreignkeys.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.EXPORTED_KEYS);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

        try {
            resultSet = jdbcMetadata.getImportedKeys(catalog, schema, table);
            if (resultSet.next()) {
                nodeText = messages.format("schema.foreignkeyconstraints.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.IMPORTED_KEYS);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.foreignkeyconstraints.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.IMPORTED_KEYS);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

        try {
            resultSet = jdbcMetadata.getTablePrivileges(catalog, schema, table);
            if (resultSet.next()) {
                nodeText = messages.format("schema.tableprivileges.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.TABLE_PRIVILIGES);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.tableprivileges.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.TABLE_PRIVILIGES);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

        try {
            resultSet = jdbcMetadata.getIndexInfo(catalog, schema, table, false, false);
            if (resultSet.next()) {
                nodeText = messages.format("schema.columnprivileges.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.COLUMN_PRIVILIGES);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.columnprivileges.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.COLUMN_PRIVILIGES);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }

    }

    private void loadJdbc30TableChildren(SchemaNode tableNode, String tableName) {

        String nodeText = null;
        SchemaNode schemaNode = null;
        ResultSet resultSet = null;

        try {
            resultSet = jdbcMetadata.getSuperTables(catalog, schema, tableName);
            if (resultSet.next()) {
                nodeText = messages.format("schema.supertables.node");
                schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.SUPER_TABLES);
                insertNodeInto(schemaNode, tableNode, 0);
            }
        } catch (SQLException sqle) {
            nodeText = messages.format("schema.supertables.node");
            schemaNode = new SchemaNode(nodeText, false, SchemaNodeType.SUPER_TABLES);
            schemaNode.setHasError(true);
            insertNodeInto(schemaNode, tableNode, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
        }
    }

    private void loadColumnChildren(SchemaNode columnNode) {

        columnNode.removeAllChildren();
        ResultSet resultSet = null;
        SchemaNode tableNode = (SchemaNode) columnNode.getParent();
        if (tableNode == null) {
            throw new IllegalArgumentException(columnNode.toString());
        }

        String tableName = tableNode.getUserObject();
        try {
            resultSet = jdbcMetadata.getColumns(catalog, schema, tableName, null);
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                SchemaNode columnNameNode = new SchemaNode(columnName.trim(), false, SchemaNodeType.COLUMN);
                insertNodeInto(columnNameNode, columnNode, 0);
            }
        } catch (SQLException sqle) {
            columnNode.setHasError(true);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ignored) {
                }
            }
            reload(columnNode);
        }
    }
}
